
-- **************************************************************************
--    Project Name:   服务质量-仲裁分析日报监控机器人推送
--    Job Name:       jms_dwm.dm_sqs_arbi_analysis_detail_dt
--    Author :        季修魁
--    date：          2023/07/05
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true;             --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  每天生成 60 个分区
set hive.exec.max.dynamic.partitions.pernode=500; --  每天生成 60 个分区

with arbitration_base_tmp as (
select code
        ,waybill_no
        ,goods_type_id
        ,goods_type_code
        ,goods_type_name
        ,goods_name
        ,goods_value
        ,first_type_code
        ,first_type
        ,second_type_code
        ,second_type
        ,exception_desc
        ,status
        ,responsibility_reply_status
        ,responsibility_confirm_status
        ,arbitration_reply_status
        ,appeal_reply_status
        ,closing_time
        ,payable_amount_total
        ,handling_fee_total
        ,award_amount_total
        ,create_by
        ,create_by_code
        ,create_by_name
        ,create_network_id
        ,create_network_code
        ,create_network_name
        ,create_time
        ,delay_day
        ,restart_count
        ,delay_adju_status
        ,order_source_code
        ,order_source_name
        ,channel
        ,audit_status
        ,first_resp_network_code
        ,second_resp_network_code
        ,third_resp_network_code
        ,four_resp_network_code
        ,five_resp_network_code
        ,duty_arr
        ,network_count
        ,arb_source
        ,express_type_code
        ,express_type_name
        ,arb_behavior
        ,customer_code
        ,customer_name
        ,dt
        ,network_code
       ,process_user_id
       ,process_user_code
       ,process_user_name
       ,process_network_id
       ,process_network_code
       ,process_network_name
  from 
(
select code
        ,waybill_no
        ,goods_type_id
        ,goods_type_code
        ,goods_type_name
        ,goods_name
        ,goods_value
        ,first_type_code
        ,first_type
        ,second_type_code
        ,second_type
        ,exception_desc
        ,status
        ,responsibility_reply_status
        ,responsibility_confirm_status
        ,arbitration_reply_status
        ,appeal_reply_status
        ,closing_time
        ,payable_amount_total
        ,handling_fee_total
        ,award_amount_total
        ,create_by
        ,create_by_code
        ,create_by_name
        ,create_network_id
        ,create_network_code
        ,create_network_name
        ,create_time
        ,delay_day
        ,restart_count
        ,delay_adju_status
        ,order_source_code
        ,order_source_name
        ,channel
        ,audit_status
        ,first_resp_network_code
        ,second_resp_network_code
        ,third_resp_network_code
        ,four_resp_network_code
        ,five_resp_network_code
        ,array(first_resp_network_code,second_resp_network_code,third_resp_network_code,four_resp_network_code,five_resp_network_code) as duty_arr
        ,case when five_resp_network_code is not null  and trim(five_resp_network_code)  !=''  then 5
              when four_resp_network_code is not null  and trim(four_resp_network_code)  !=''  then 4
              when third_resp_network_code is not null and trim(third_resp_network_code) !='' then 3
              when second_resp_parent_code is not null and trim(second_resp_parent_code) !='' then 2
              when first_resp_network_code is not null and trim(first_resp_network_code) !='' then 1
              else 0
         end as network_count
        ,arb_source
        ,express_type_code
        ,express_type_name
        ,arb_behavior
        ,customer_code
        ,customer_name
        ,process_user_id
        ,process_user_code
        ,process_user_name
        ,process_network_id
        ,process_network_code
        ,process_network_name
        ,dt
   from jms_dwd.dwd_arbitration_base_dt
  where dt >= add_months(trunc('{{ execution_date | cst_ds }}','MM'),-2) and dt<='{{ execution_date | cst_ds }}'
    ) t LATERAL VIEW explode(duty_arr) netd as network_code
    where netd.network_code is not null
)

insert overwrite table jms_Dm.dm_sqs_arbi_analysis_detail_dt partition (dt)
select a.code
       ,a.waybill_no
       ,a.goods_type_id
       ,a.goods_type_code
       ,a.goods_type_name
       ,a.goods_name
       ,a.goods_value
       ,a.first_type_code
       ,a.first_type
       ,a.second_type_code
       ,a.second_type
       ,a.exception_desc
       ,a.status
       ,a.responsibility_reply_status
       ,a.responsibility_confirm_status
       ,a.arbitration_reply_status
       ,a.appeal_reply_status
       ,a.closing_time
       ,a.payable_amount_total
       ,a.handling_fee_total
       ,a.award_amount_total
       ,a.create_by
       ,a.create_by_code
       ,a.create_by_name
       ,a.create_network_id
       ,a.create_network_code
       ,a.create_network_name
       ,a.create_time
       ,a.delay_day
       ,a.restart_count
       ,a.delay_adju_status
       ,a.order_source_code
       ,a.order_source_name
       ,a.channel
       ,a.audit_status
       ,a.first_resp_network_code
       ,a.second_resp_network_code
       ,a.third_resp_network_code
       ,a.four_resp_network_code
       ,a.five_resp_network_code
       ,a.network_code  as duty_network_code
       ,e.name          as duty_network_name
       ,a.network_count as duty_network_count
       ,a.arb_source
       ,a.express_type_code
       ,a.express_type_name
       ,a.arb_behavior
       ,a.customer_code
       ,a.customer_name
       ,b.first_center_loading_send_shipment_no
       ,c.shipment_name
       ,c.start_code
       ,c.start_name
       ,c.end_code
       ,c.end_name
       ,b.end_center_unloading_arrival_shipment_no
       ,d.shipment_name
       ,d.start_code
       ,d.start_name
       ,d.end_code
       ,d.end_name       
       ,e.network_type            as duty_network_type
       ,e.manage_code             as duty_manage_code
       ,e.manage_name             as duty_manage_name
       ,e.virt_code               as duty_virt_code
       ,e.virt_name               as duty_virt_name
       ,e.agent_code              as duty_agent_code
       ,e.agent_name              as duty_agent_name
       ,e.zone_code               as duty_zone_code
       ,e.zone_name               as duty_zone_name    
       ,f.network_type            as create_network_type
       ,f.manage_code             as create_manage_code
       ,f.manage_name             as create_manage_name
       ,f.virt_code               as create_virt_code
       ,f.virt_name               as create_virt_name
       ,f.agent_code              as create_agent_code
       ,f.agent_name              as create_agent_name
       ,f.zone_code               as create_zone_code
       ,f.zone_name               as create_zone_name
       ,a.process_user_id
       ,a.process_user_code
       ,a.process_user_name
       ,a.process_network_id
       ,a.process_network_code
       ,a.process_network_name
       ,a.dt
from arbitration_base_tmp   a 
left join (
select waybill_no
   ,max(first_center_loading_send_shipment_no)    as first_center_loading_send_shipment_no
   ,max(end_center_unloading_arrival_shipment_no) as end_center_unloading_arrival_shipment_no
from jms_dwd.dwd_wide_unsign_summary_waybill_dt
where dt >= date_sub('{{ execution_date | cst_ds }}',75) and dt<='{{ execution_date | cst_ds }}'
 group by waybill_no
 ) b    
on a.waybill_no = b.waybill_no
left join (select shipment_no,shipment_name,start_code,start_name,end_code,end_name 
             from jms_dwd.dwd_tmsnew_shipment_union_base_dt
            where dt>=date_sub('{{ execution_date | cst_ds }}',75) and dt<='{{ execution_date | cst_ds }}'
              and table_type =1
           ) c
  on b.first_center_loading_send_shipment_no= c.shipment_no
left join (select shipment_no,shipment_name,start_code,start_name,end_code,end_name 
             from jms_dwd.dwd_tmsnew_shipment_union_base_dt
            where dt>=date_sub('{{ execution_date | cst_ds }}',75) and dt<='{{ execution_date | cst_ds }}'
              and table_type =1) d
  on b.end_center_unloading_arrival_shipment_no= d.shipment_no
left join jms_dim.dim_network_whole_massage e on a.network_code = e.code   
left join jms_dim.dim_network_whole_massage f on a.create_network_code = f.code
distribute by dt
;